﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ArbabTravelsERP.DAL;
using ArbabTravelsERP.Models;
using System.IO;
using System.Configuration;

namespace ArbabTravelsERP.Controllers
{
    public class EmployeeController : Controller
    {
        string DocPath = ConfigurationManager.AppSettings["Doc_Upload"].ToString();
        string DocStr = ConfigurationManager.ConnectionStrings["xlsConnectionString"].ConnectionString.ToString();
        string DocStr_XLSX = ConfigurationManager.ConnectionStrings["xlsxConnectionString"].ConnectionString.ToString();

        DAL.Master dal = new Master();
        CandidateDAL candDAL = new CandidateDAL();

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult Create()
        {
            CandidateModel cand = new CandidateModel();
            DataSet ds = new DataSet();

            ViewData["RegNo"] = "EM" + DateTime.Now.ToString("yyyyMMddhhmmss"); ;

            Session["SaveAddressDetails"] = ds;
            cand.GetAddressDetails = ds;
            GetAllMaster();

            return View(cand);
        }

        private void GetAllMaster()
        {
            All_Master master = new All_Master();
            ViewData["GetCountryStateCity"] = master.GetCountryStateCity();
            ViewData["GetAvailingType"] = master.GetAvailingType();
            ViewData["GetSource"] = master.GetSource();
            ViewData["GetStatus"] = master.GetStatus();
            ViewData["GetRequirementType"] = master.GetRequirementType();
            ViewData["GetVisaType"] = master.GetVisaType();
            ViewData["GetMaritalStatus"] = master.GetMaritalStatus();
            ViewData["GetLocation"] = master.GetLocation();
            ViewData["GetNationality"] = master.GetNationality();
            ViewData["GetReligion"] = master.GetReligion();
            ViewData["GetLanguage"] = master.GetLanguage();
            ViewData["GetAddressType"] = master.GetAddressType();
            ViewData["GetCompany"] = master.GetCompany();
            ViewData["GetDesignation"] = master.GetDesignation();
            ViewData["GetIndustry"] = master.GetIndustry();
            ViewData["GetUniversity"] = master.GetUniversity();
            ViewData["GetEducation"] = master.GetEducation();
            ViewData["GetSpecialization"] = master.GetSpecialization();
            ViewData["GetVehicleType"] = master.GetVehicleType();
            ViewData["GetBranch"] = master.GetBranch();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Create(CandidateModel cand)
        {
            if (ModelState.IsValid)
            {
                var filePath = string.Empty;
                if (!string.IsNullOrEmpty(cand.USER_IMAGE_PATH))
                {
                    var path = string.Empty;
                    filePath = Convert.ToString("..\\EmployeeImage\\" + DateTime.Now.ToString("yyyyMMddhhmmss")) + "_" + Path.GetFileName(cand.USER_IMAGE_PATH.ToString());
                    var fileName = Convert.ToString(DateTime.Now.ToString("yyyyMMddhhmmss")) + "_" + Path.GetFileName(cand.USER_IMAGE_PATH.ToString());
                    FileInfo file = new FileInfo(Server.MapPath("~/EmployeeImage/" + Convert.ToString(cand.REGISTRATION_NO) + "_" + Convert.ToString(fileName)));

                    if (file.Exists)
                    {
                        file.Delete();
                    }

                    FileInfo file1 = new FileInfo(cand.USER_IMAGE_PATH);
                    path = Path.Combine(Server.MapPath("~/EmployeeImage/") + "_" + Convert.ToString(fileName));
                    file1.CopyTo(path);

                }

                DataTable dt = null;
                dt = candDAL.InsertCandidateDetails(cand, filePath);
                candDAL.InsertCandidatePersonalDetails(cand);
               
                DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];
                if (dtaddress != null)
                {
                    #region Save Address

                    //===================Saving ADDRESS Details=================================//
                    if (dtaddress.Tables["AddressDetails"] != null)
                    {
                        if (dtaddress.Tables["AddressDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["AddressDetails"].Rows.Count; i++)
                            {
                                cand.ADDRESS_TYPE_ID = Convert.ToInt32(dtaddress.Tables["AddressDetails"].Rows[i]["ADDRESS_TYPE_ID"].ToString());
                                cand.ADDRESS = Convert.ToString(dtaddress.Tables["AddressDetails"].Rows[i]["Address"].ToString());
                                cand.CITY_CODE = Convert.ToString(dtaddress.Tables["AddressDetails"].Rows[i]["CITY_CODE"].ToString());
                                cand.PIN_CODE = Convert.ToString(dtaddress.Tables["AddressDetails"].Rows[i]["Pin_Code"].ToString());
                                cand.VILLAGE = Convert.ToString(dtaddress.Tables["AddressDetails"].Rows[i]["Village"].ToString());

                                candDAL.SaveAddressDetails(cand, "SAVE_ADDRESS");
                            }
                        }
                    }
                    //===================Saving ADDRESS Details=================================//

                    #endregion

                    #region Save Phone

                    //===================Saving Phone Details=================================//
                    if (dtaddress.Tables["PhoneDetails"] != null)
                    {
                        if (dtaddress.Tables["PhoneDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["PhoneDetails"].Rows.Count; i++)
                            {
                                cand.PHONE_TYPE_ID = Convert.ToString(dtaddress.Tables["PhoneDetails"].Rows[i]["PHONE_TYPE_ID"].ToString());
                                cand.PHONE_NO = Convert.ToString(dtaddress.Tables["PhoneDetails"].Rows[i]["PHONE_NO"].ToString());

                                candDAL.SaveContactDetails(cand, "SAVE_PHONE");
                            }
                        }
                    }
                    //===================Saving Phone Details=================================//
                    #endregion

                    #region Save Email

                    //===================Saving Email Details=================================//
                    if (dtaddress.Tables["EmailDetails"] != null)
                    {
                        if (dtaddress.Tables["EmailDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["EmailDetails"].Rows.Count; i++)
                            {
                                cand.EMAIL_ID = Convert.ToString(dtaddress.Tables["EmailDetails"].Rows[i]["EMAIL_ID"].ToString());

                                candDAL.SaveEmailDetails(cand, "SAVE_EMAIL");
                            }
                        }
                    }
                    //===================Saving Email Details=================================//
                    #endregion

                    #region Save Certification

                    //===================Saving Certification Details=================================//
                    if (dtaddress.Tables["CertificationDetails"] != null)
                    {
                        if (dtaddress.Tables["CertificationDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["CertificationDetails"].Rows.Count; i++)
                            {
                                cand.CERTIFICATION = Convert.ToString(dtaddress.Tables["CertificationDetails"].Rows[i]["CERTIFICATION"].ToString());
                                cand.CERTIFICATION_LEVEL = Convert.ToString(dtaddress.Tables["CertificationDetails"].Rows[i]["CERTIFICATION_LEVEL"].ToString());
                                cand.INSTITUTE_NAME = Convert.ToString(dtaddress.Tables["CertificationDetails"].Rows[i]["INSTITUTE_NAME"].ToString());
                                cand.INSTITUE_YEAR_OF_PASSIN = Convert.ToString(dtaddress.Tables["CertificationDetails"].Rows[i]["INSTITUE_YEAR_OF_PASSING"].ToString());

                                candDAL.Save_Certification_Details(cand, "SAVE_CERTIFICATION");
                            }
                        }
                    }
                    //===================Saving Certification Details=================================//
                    #endregion

                    #region Save Education

                    //===================Saving EDUCATION Details=================================//
                    if (dtaddress.Tables["EducationDetails"] != null)
                    {
                        if (dtaddress.Tables["EducationDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["EducationDetails"].Rows.Count; i++)
                            {
                                cand.EDUCATION_TYPE_ID = Convert.ToInt32(dtaddress.Tables["EducationDetails"].Rows[i]["EDUCATION_TYPE_ID"].ToString());
                                cand.SPECIALIZATION_TYPE_ID = Convert.ToInt32(dtaddress.Tables["EducationDetails"].Rows[i]["SPECIALIZATION_TYPE_ID"].ToString());
                                cand.UNIVERSITY_ID = Convert.ToInt32(dtaddress.Tables["EducationDetails"].Rows[i]["UNIVERSITY_ID"].ToString());
                                cand.UNIVERSITY_YEAR_OF_PASSING = Convert.ToString(dtaddress.Tables["EducationDetails"].Rows[i]["UNIVERSITY_YEAR_OF_PASSING"].ToString());

                                candDAL.Save_Education_Details(cand, "SAVE_EDUCATION");
                            }
                        }
                    }
                    //===================Saving EDUCATION Details=================================//
                    #endregion

                    #region Save Work

                    //===================Saving Work Details=================================//
                    if (dtaddress.Tables["WorkDetails"] != null)
                    {
                        if (dtaddress.Tables["WorkDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["WorkDetails"].Rows.Count; i++)
                            {
                                bool exp, current_status;
                                if (Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["IS_EXPERIENCED"].ToString()) == "Experience")
                                {
                                    exp = true;
                                }
                                else
                                    exp = false;

                                if (Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["IS_CURRENT_STATUS"].ToString()) == "Yes")
                                {
                                    current_status = true;
                                }
                                else
                                    current_status = false;

                                cand.USER_COMPANY_NAME = Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["USER_COMPANY_NAME"].ToString());
                                cand.DESIGNATION_ID = Convert.ToInt32(dtaddress.Tables["WorkDetails"].Rows[i]["DESIGNATION_ID"].ToString());
                                cand.INDUSTRY_ID = Convert.ToInt32(dtaddress.Tables["WorkDetails"].Rows[i]["INDUSTRY_ID"].ToString());
                                cand.WORK_PERIOD_FROM = Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["WORK_PERIOD_FROM"].ToString());
                                cand.WORK_PERIOD_TO = Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["WORK_PERIOD_TO"].ToString());
                                cand.WORK_EXP_CITY_CODE = Convert.ToString(dtaddress.Tables["WorkDetails"].Rows[i]["WORK_EXP_CITY_CODE"].ToString());
                                cand.IS_EXPERIENCED = exp;
                                cand.IS_CURRENT_COMPANY = current_status;

                                candDAL.Save_Work_Details(cand, "SAVE_WORK");
                            }
                        }
                    }
                    //===================Saving Work Details=================================//

                    #endregion

                    #region Save Document

                    //===================Saving Document Details=================================//
                    if (dtaddress.Tables["DocDetails"] != null)
                    {
                        if (dtaddress.Tables["DocDetails"].Rows.Count > 0)
                        {
                            for (int i = 0; i < dtaddress.Tables["DocDetails"].Rows.Count; i++)
                            {
                                cand.DOCUMENT_TYPE_ID = Convert.ToString(dtaddress.Tables["DocDetails"].Rows[i]["DOC_TYPE"].ToString());
                                cand.DOCUMENT_PATH = Convert.ToString(dtaddress.Tables["DocDetails"].Rows[i]["PATH"].ToString());

                                candDAL.Save_Document_Details(cand, "SAVE_DOC");
                            }
                        }
                    }
                    //===================Saving Document Details=================================//
                    #endregion


                }

                if (dt.Rows[0]["Error"].ToString() == "Successfull")
                {
                    DataSet ds = new DataSet();
                    TempData["Success"] = "Success! Well done its submitted.";

                    return RedirectToAction("Create");
                }

            }
            ViewData["RegNo"] = cand.REGISTRATION_NO;
            GetAllMaster();
            return View(cand);
        }

        #region Master

        public JsonResult GetCityByStateCode(string State_code)
        {
            if (System.Web.HttpContext.Current.Cache["dtCountryStateCity"] == null)
            {
                System.Web.HttpContext.Current.Cache["dtCountryStateCity"] = dal.GetCountryStateCity();
            }

            DataTable dtcity = new DataTable();
            if (((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("State_Code='" + State_code + "'").Length > 0)
                dtcity = ((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("State_Code='" + State_code + "'").CopyToDataTable();

            var city = (from city_code in dtcity.AsEnumerable()
                        select new SelectListItem
                        {
                            Value = city_code.Field<string>("City_code"),
                            Text = city_code.Field<string>("City_name")
                        }).ToList();

            return Json(new SelectList(city, "Value", "Text"));
        }

        public JsonResult GetStateByCountryCode(string Country_code)
        {
            if (System.Web.HttpContext.Current.Cache["dtCountryStateCity"] == null)
            {
                System.Web.HttpContext.Current.Cache["dtCountryStateCity"] = dal.GetCountryStateCity();
            }

            DataTable dtcity = new DataTable();
            if (((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("Country_Code='" + Country_code + "'").Length > 0)
                dtcity = ((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("Country_Code='" + Country_code + "'").CopyToDataTable();

            var city = (from city_code in dtcity.AsEnumerable()
                        select new SelectListItem
                        {
                            Value = city_code.Field<string>("State_code"),
                            Text = city_code.Field<string>("State_name")
                        }).ToList();

            return Json(new SelectList(city, "Value", "Text"));
        }

        #endregion

        #region Address

        public JsonResult SaveAddressDetails(int address_type, string address, string citycode, string pincode, string village, string regno)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();

            if (System.Web.HttpContext.Current.Cache["dtCountryStateCity"] == null)
            {
                System.Web.HttpContext.Current.Cache["dtCountryStateCity"] = dal.GetCountryStateCity();
            }
            DataTable dtcity = ((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("CITY_CODE='" + citycode + "'").CopyToDataTable();
            string country_name = dtcity.Rows[0]["COUNTRY_NAME"].ToString();
            string statecity = dtcity.Rows[0]["STATE_NAME"].ToString() + "-" + dtcity.Rows[0]["CITY_NAME"].ToString();

            DataTable dtAddressType = ((DataTable)System.Web.HttpContext.Current.Cache["AddressType"]).Select("ADDRESS_TYPE_ID='" + address_type + "'").CopyToDataTable();
            string addresstype = dtAddressType.Rows[0]["ADDRESS_TYPE"].ToString();

            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["AddressDetails"] != null)
            {
                if (dtaddress.Tables["AddressDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["AddressDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;
                    newRow["ADDRESS_TYPE_ID"] = address_type;
                    newRow["Address"] = addresstype + "-" + address;
                    newRow["CITY_CODE"] = citycode;
                    newRow["Pin_Code"] = pincode;
                    newRow["Village"] = village;
                    newRow["Country"] = country_name;
                    newRow["StateCity"] = statecity;
                    dtaddress.Tables["AddressDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["AddressDetails"].NewRow();
                    // newRow["ID"] =  1;
                    newRow["ADDRESS_TYPE_ID"] = address_type;
                    newRow["Address"] = addresstype + "-" + address;
                    newRow["CITY_CODE"] = citycode;
                    newRow["Pin_Code"] = pincode;
                    newRow["Village"] = village;
                    newRow["Country"] = country_name;
                    newRow["StateCity"] = statecity;
                    dtaddress.Tables["AddressDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "AddressDetails";


                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("ADDRESS_TYPE_ID");
                DC = dt.Columns.Add("Address");
                DC = dt.Columns.Add("CITY_CODE");
                DC = dt.Columns.Add("Pin_Code");
                DC = dt.Columns.Add("Village");
                DC = dt.Columns.Add("Country");
                DC = dt.Columns.Add("StateCity");


                DataRow newRow = dt.NewRow();
                // newRow["ID"] =  1;
                newRow["ADDRESS_TYPE_ID"] = address_type;
                newRow["Address"] = addresstype + "-" + address;
                newRow["CITY_CODE"] = citycode;
                newRow["Pin_Code"] = pincode;
                newRow["Village"] = village;
                newRow["Country"] = country_name;
                newRow["StateCity"] = statecity;

                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["AddressDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeleteAddressDetails(int addressid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["AddressDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == addressid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["AddressDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        #endregion

        #region Email

        public JsonResult SaveEmailDetails(string EmailId)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["EmailDetails"] != null)
            {
                if (dtaddress.Tables["EmailDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["EmailDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;
                    newRow["EMAIL_ID"] = EmailId;
                    dtaddress.Tables["EmailDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["EmailDetails"].NewRow();
                    // newRow["ID"] =  1;
                    newRow["EMAIL_ID"] = EmailId;
                    dtaddress.Tables["EmailDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "EmailDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("EMAIL_ID");

                DataRow newRow = dt.NewRow();
                // newRow["ID"] =  1;
                newRow["EMAIL_ID"] = EmailId;

                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["EmailDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeleteEmailDetails(int emailid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["EmailDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == emailid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["EmailDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        #endregion

        #region phone
        public JsonResult SavePhoneDetails(string PHONE_TYPE_ID, string PHONE_NO)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["PhoneDetails"] != null)
            {
                if (dtaddress.Tables["PhoneDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["PhoneDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;

                    newRow["PHONE_TYPE_ID"] = PHONE_TYPE_ID;
                    newRow["PHONE_NO"] = PHONE_NO;
                    dtaddress.Tables["PhoneDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["PhoneDetails"].NewRow();
                    // newRow["ID"] =  1;
                    newRow["PHONE_TYPE_ID"] = PHONE_TYPE_ID;
                    newRow["PHONE_NO"] = PHONE_NO;
                    dtaddress.Tables["PhoneDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "PhoneDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("PHONE_TYPE_ID");
                DC = dt.Columns.Add("PHONE_NO");

                DataRow newRow = dt.NewRow();
                // newRow["ID"] =  1;
                newRow["PHONE_TYPE_ID"] = PHONE_TYPE_ID;
                newRow["PHONE_NO"] = PHONE_NO;

                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["PhoneDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeletePhoneDetails(int phoneid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["PhoneDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == phoneid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["PhoneDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        #endregion

        #region Certification


        public JsonResult SaveCertificationDetails(string CERTIFICATION, string CERTIFICATION_LEVEL, string INSTITUTE_NAME, string INSTITUE_YEAR_OF_PASSING)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["CertificationDetails"] != null)
            {
                if (dtaddress.Tables["CertificationDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["CertificationDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;

                    newRow["CERTIFICATION"] = CERTIFICATION;
                    newRow["CERTIFICATION_LEVEL"] = CERTIFICATION_LEVEL;
                    newRow["INSTITUTE_NAME"] = INSTITUTE_NAME;
                    newRow["INSTITUE_YEAR_OF_PASSING"] = INSTITUE_YEAR_OF_PASSING;
                    dtaddress.Tables["CertificationDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["CertificationDetails"].NewRow();
                    newRow["CERTIFICATION"] = CERTIFICATION;
                    newRow["CERTIFICATION_LEVEL"] = CERTIFICATION_LEVEL;
                    newRow["INSTITUTE_NAME"] = INSTITUTE_NAME;
                    newRow["INSTITUE_YEAR_OF_PASSING"] = INSTITUE_YEAR_OF_PASSING;
                    dtaddress.Tables["CertificationDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "CertificationDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("CERTIFICATION");
                DC = dt.Columns.Add("CERTIFICATION_LEVEL");
                DC = dt.Columns.Add("INSTITUTE_NAME");
                DC = dt.Columns.Add("INSTITUE_YEAR_OF_PASSING");

                DataRow newRow = dt.NewRow();
                newRow["CERTIFICATION"] = CERTIFICATION;
                newRow["CERTIFICATION_LEVEL"] = CERTIFICATION_LEVEL;
                newRow["INSTITUTE_NAME"] = INSTITUTE_NAME;
                newRow["INSTITUE_YEAR_OF_PASSING"] = INSTITUE_YEAR_OF_PASSING;
                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["CertificationDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeleteCertificationDetails(int certificationid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["CertificationDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == certificationid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["CertificationDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        #endregion

        #region Education


        public JsonResult SaveEducationDetails(string EDUCATION_TYPE_ID, string SPECIALIZATION_TYPE_ID, string UNIVERSITY_ID, string UNIVERSITY_YEAR_OF_PASSING)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();

            if (System.Web.HttpContext.Current.Cache["Education"] == null)
            {
                System.Web.HttpContext.Current.Cache["Education"] = dal.GetEducation();
            }
            DataTable dteducation = ((DataTable)System.Web.HttpContext.Current.Cache["Education"]).Select("EDUCATION_TYPE_ID='" + Convert.ToInt32(EDUCATION_TYPE_ID) + "'").CopyToDataTable();
            string education = dteducation.Rows[0]["EDUCATION_TYPE"].ToString();

            if (System.Web.HttpContext.Current.Cache["Specialization"] == null)
            {
                System.Web.HttpContext.Current.Cache["Specialization"] = dal.GetSpecialization();
            }
            DataTable dtspecialization = ((DataTable)System.Web.HttpContext.Current.Cache["Specialization"]).Select("SPECIALIZATION_ID='" + Convert.ToInt32(SPECIALIZATION_TYPE_ID) + "'").CopyToDataTable();
            string specialization = dtspecialization.Rows[0]["SPECIALIZATION_TYPE"].ToString();

            if (System.Web.HttpContext.Current.Cache["University"] == null)
            {
                System.Web.HttpContext.Current.Cache["University"] = dal.GetUniversity();
            }
            DataTable dtuniversity = ((DataTable)System.Web.HttpContext.Current.Cache["University"]).Select("UNIVERSITY_ID='" + Convert.ToInt32(UNIVERSITY_ID) + "'").CopyToDataTable();
            string university = dtuniversity.Rows[0]["UNIVERSITY_NAME"].ToString();

            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["EducationDetails"] != null)
            {
                if (dtaddress.Tables["EducationDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["EducationDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;

                    newRow["EDUCATION_TYPE_ID"] = EDUCATION_TYPE_ID;
                    newRow["SPECIALIZATION_TYPE_ID"] = SPECIALIZATION_TYPE_ID;
                    newRow["UNIVERSITY_ID"] = UNIVERSITY_ID;
                    newRow["UNIVERSITY_YEAR_OF_PASSING"] = UNIVERSITY_YEAR_OF_PASSING;
                    newRow["EDUCATION_TYPE"] = education;
                    newRow["SPECIALIZATION_TYPE"] = specialization;
                    newRow["UNIVERSITY"] = university;
                    dtaddress.Tables["EducationDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["EducationDetails"].NewRow();
                    newRow["EDUCATION_TYPE_ID"] = EDUCATION_TYPE_ID;
                    newRow["SPECIALIZATION_TYPE_ID"] = SPECIALIZATION_TYPE_ID;
                    newRow["UNIVERSITY_ID"] = UNIVERSITY_ID;
                    newRow["UNIVERSITY_YEAR_OF_PASSING"] = UNIVERSITY_YEAR_OF_PASSING;
                    newRow["EDUCATION_TYPE"] = education;
                    newRow["SPECIALIZATION_TYPE"] = specialization;
                    newRow["UNIVERSITY"] = university;
                    dtaddress.Tables["EducationDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "EducationDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("EDUCATION_TYPE_ID");
                DC = dt.Columns.Add("SPECIALIZATION_TYPE_ID");
                DC = dt.Columns.Add("UNIVERSITY_ID");
                DC = dt.Columns.Add("UNIVERSITY_YEAR_OF_PASSING");
                DC = dt.Columns.Add("EDUCATION_TYPE");
                DC = dt.Columns.Add("SPECIALIZATION_TYPE");
                DC = dt.Columns.Add("UNIVERSITY");

                DataRow newRow = dt.NewRow();
                newRow["EDUCATION_TYPE_ID"] = EDUCATION_TYPE_ID;
                newRow["SPECIALIZATION_TYPE_ID"] = SPECIALIZATION_TYPE_ID;
                newRow["UNIVERSITY_ID"] = UNIVERSITY_ID;
                newRow["UNIVERSITY_YEAR_OF_PASSING"] = UNIVERSITY_YEAR_OF_PASSING;
                newRow["EDUCATION_TYPE"] = education;
                newRow["SPECIALIZATION_TYPE"] = specialization;
                newRow["UNIVERSITY"] = university;
                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["EducationDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeleteEducationDetails(int educationid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["EducationDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == educationid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["EducationDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        #endregion

        #region Work


        public JsonResult SaveWorkDetails(string IS_EXPERIENCED, string IS_CURRENT_STATUS, string USER_COMPANY_NAME, string DESIGNATION_ID, string INDUSTRY_ID, string WORK_PERIOD_FROM, string WORK_PERIOD_TO, string WORK_EXP_CITY_CODE)
        {
            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();

            if (string.IsNullOrEmpty(WORK_PERIOD_FROM))
            {
                WORK_PERIOD_FROM = "-";
            }
            if (string.IsNullOrEmpty(WORK_PERIOD_TO))
            {
                WORK_PERIOD_TO = "-";
            }

            if (System.Web.HttpContext.Current.Cache["Company"] == null)
            {
                System.Web.HttpContext.Current.Cache["Company"] = dal.GetCompany();
            }
            DataTable dtcompany = ((DataTable)System.Web.HttpContext.Current.Cache["Company"]).Select("COMPANY_ID='" + Convert.ToInt32(USER_COMPANY_NAME) + "'").CopyToDataTable();
            string company = dtcompany.Rows[0]["COMPANY_NAME"].ToString();

            if (System.Web.HttpContext.Current.Cache["Industry"] == null)
            {
                System.Web.HttpContext.Current.Cache["Industry"] = dal.GetIndustry();
            }
            DataTable dtindustry = ((DataTable)System.Web.HttpContext.Current.Cache["Industry"]).Select("INDUSTRY_ID='" + Convert.ToInt32(INDUSTRY_ID) + "'").CopyToDataTable();
            string industry = dtindustry.Rows[0]["INDUSTRY_TYPE"].ToString();

            if (System.Web.HttpContext.Current.Cache["Designation"] == null)
            {
                System.Web.HttpContext.Current.Cache["Designation"] = dal.GetDesignation();
            }
            DataTable dtdesignation = ((DataTable)System.Web.HttpContext.Current.Cache["Designation"]).Select("DESIGNATION_ID='" + Convert.ToInt32(DESIGNATION_ID) + "'").CopyToDataTable();
            string designation = dtdesignation.Rows[0]["DESIGNATION_NAME"].ToString();

            if (System.Web.HttpContext.Current.Cache["dtCountryStateCity"] == null)
            {
                System.Web.HttpContext.Current.Cache["dtCountryStateCity"] = dal.GetCountryStateCity();
            }
            string address = string.Empty;
            if (!string.IsNullOrEmpty(WORK_EXP_CITY_CODE))
            {
                DataTable dtcity = ((DataTable)System.Web.HttpContext.Current.Cache["dtCountryStateCity"]).Select("CITY_CODE='" + Convert.ToString(WORK_EXP_CITY_CODE) + "'").CopyToDataTable();
                address = dtcity.Rows[0]["COUNTRY_NAME"].ToString() + "-" + dtcity.Rows[0]["STATE_NAME"].ToString() + "-" + dtcity.Rows[0]["CITY_NAME"].ToString(); ;
            }
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["WorkDetails"] != null)
            {
                if (dtaddress.Tables["WorkDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["WorkDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;

                    newRow["USER_COMPANY_NAME"] = USER_COMPANY_NAME;
                    newRow["DESIGNATION_ID"] = DESIGNATION_ID;
                    newRow["INDUSTRY_ID"] = INDUSTRY_ID;
                    newRow["WORK_PERIOD_FROM"] = WORK_PERIOD_FROM;
                    newRow["WORK_PERIOD_TO"] = WORK_PERIOD_TO;
                    newRow["WORK_EXP_CITY_CODE"] = WORK_EXP_CITY_CODE;
                    newRow["COMPANY"] = company;
                    newRow["DESIGNATION"] = designation;
                    newRow["COMPANY"] = company;
                    newRow["INDUSTRY"] = industry;
                    newRow["ADDRESS"] = address;
                    newRow["IS_EXPERIENCED"] = IS_EXPERIENCED;
                    newRow["IS_CURRENT_STATUS"] = IS_CURRENT_STATUS;
                    newRow["WORK_PERIOD"] = Convert.ToString(WORK_PERIOD_FROM) + "-" + Convert.ToString(WORK_PERIOD_TO);
                    dtaddress.Tables["WorkDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["WorkDetails"].NewRow();
                    newRow["USER_COMPANY_NAME"] = USER_COMPANY_NAME;
                    newRow["DESIGNATION_ID"] = DESIGNATION_ID;
                    newRow["INDUSTRY_ID"] = INDUSTRY_ID;
                    newRow["WORK_PERIOD_FROM"] = WORK_PERIOD_FROM;
                    newRow["WORK_PERIOD_TO"] = WORK_PERIOD_TO;
                    newRow["WORK_EXP_CITY_CODE"] = WORK_EXP_CITY_CODE;
                    newRow["COMPANY"] = company;
                    newRow["DESIGNATION"] = designation;
                    newRow["INDUSTRY"] = industry;
                    newRow["ADDRESS"] = address;
                    newRow["IS_EXPERIENCED"] = IS_EXPERIENCED;
                    newRow["IS_CURRENT_STATUS"] = IS_CURRENT_STATUS;
                    newRow["WORK_PERIOD"] = Convert.ToString(WORK_PERIOD_FROM) + "-" + Convert.ToString(WORK_PERIOD_TO);
                    dtaddress.Tables["EducationDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "WorkDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("USER_COMPANY_NAME");
                DC = dt.Columns.Add("DESIGNATION_ID");
                DC = dt.Columns.Add("INDUSTRY_ID");
                DC = dt.Columns.Add("WORK_PERIOD_FROM");
                DC = dt.Columns.Add("WORK_PERIOD_TO");
                DC = dt.Columns.Add("WORK_EXP_CITY_CODE");
                DC = dt.Columns.Add("COMPANY");
                DC = dt.Columns.Add("DESIGNATION");
                DC = dt.Columns.Add("INDUSTRY");
                DC = dt.Columns.Add("ADDRESS");
                DC = dt.Columns.Add("IS_EXPERIENCED");
                DC = dt.Columns.Add("IS_CURRENT_STATUS");
                DC = dt.Columns.Add("WORK_PERIOD");

                DataRow newRow = dt.NewRow();
                newRow["USER_COMPANY_NAME"] = USER_COMPANY_NAME;
                newRow["DESIGNATION_ID"] = DESIGNATION_ID;
                newRow["INDUSTRY_ID"] = INDUSTRY_ID;
                newRow["WORK_PERIOD_FROM"] = WORK_PERIOD_FROM;
                newRow["WORK_PERIOD_TO"] = WORK_PERIOD_TO;
                newRow["WORK_EXP_CITY_CODE"] = WORK_EXP_CITY_CODE;
                newRow["COMPANY"] = company;
                newRow["DESIGNATION"] = designation;
                newRow["INDUSTRY"] = industry;
                newRow["ADDRESS"] = industry;
                newRow["IS_EXPERIENCED"] = IS_EXPERIENCED;
                newRow["IS_CURRENT_STATUS"] = IS_CURRENT_STATUS;
                newRow["WORK_PERIOD"] = Convert.ToString(WORK_PERIOD_FROM) + "-" + Convert.ToString(WORK_PERIOD_TO);
                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["WorkDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

        public JsonResult DeleteWorkDetails(int workid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["WorkDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == workid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["WorkDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }
        #endregion

        public static string GetJson(DataTable dt)
        {
            System.Web.Script.Serialization.JavaScriptSerializer serializer =
               new System.Web.Script.Serialization.JavaScriptSerializer();
            List<Dictionary<string, object>> rows =
               new List<Dictionary<string, object>>();
            Dictionary<string, object> row = null;

            foreach (DataRow dr in dt.Rows)
            {
                row = new Dictionary<string, object>();
                foreach (DataColumn col in dt.Columns)
                {
                    row.Add(col.ColumnName, dr[col]);
                }
                rows.Add(row);
            }
            return serializer.Serialize(rows);
        }

        private static int GetExcelUploadFileSize(string fileSize)
        {
            int MaxfileSize = Convert.ToInt32(Math.Round(Convert.ToDouble(fileSize), MidpointRounding.AwayFromZero));
            return MaxfileSize;
        }

        public JsonResult UploadFile(string docpath, string doctype, string regno)
        {
            var path = string.Empty;
            var fileName = Convert.ToString(regno) + "_" + Path.GetFileName(docpath.ToString());

            if (docpath != null || docpath != string.Empty)
            {
                FileInfo file = new FileInfo(Server.MapPath("~/DocUploadedFiles/" + Convert.ToString(regno) + "_" + Convert.ToString(fileName)));

                if (file.Exists)
                {
                    file.Delete();
                }

                FileInfo file1 = new FileInfo(docpath);
                path = Path.Combine(Server.MapPath("~/DocUploadedFiles/"), Convert.ToString(regno) + "_" + Convert.ToString(fileName));
                file1.CopyTo(path);

            }

            JsonResult json = new JsonResult();
            DataSet ds = new DataSet();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            if (dtaddress.Tables["DocDetails"] != null)
            {
                if (dtaddress.Tables["DocDetails"].Rows.Count > 0)
                {
                    DataRow newRow = dtaddress.Tables["DocDetails"].NewRow();
                    // newRow["ID"] = dtaddress.Tables["AddressDetails"].Rows.Count + 1;

                    newRow["DOC_TYPE"] = doctype;
                    newRow["PATH"] = path;
                    newRow["FILENAME"] = fileName;
                    dtaddress.Tables["DocDetails"].Rows.Add(newRow);

                }
                else
                {
                    DataRow newRow = dtaddress.Tables["DocDetails"].NewRow();
                    newRow["DOC_TYPE"] = doctype;
                    newRow["PATH"] = path;
                    newRow["FILENAME"] = fileName;
                    dtaddress.Tables["DocDetails"].Rows.Add(newRow);
                }

            }
            else
            {
                DataTable dt = new DataTable();
                dt.TableName = "DocDetails";

                DataColumn DC = new DataColumn();
                DataColumn id = new DataColumn("ID", typeof(Int32));
                //  DC = dt.Columns.Add("ID");
                id.AutoIncrement = true;
                id.AutoIncrementSeed = 1;
                id.AutoIncrementStep = 1;
                dt.Columns.Add(id);
                DC = dt.Columns.Add("DOC_TYPE");
                DC = dt.Columns.Add("PATH");
                DC = dt.Columns.Add("FILENAME");

                DataRow newRow = dt.NewRow();
                newRow["DOC_TYPE"] = doctype;
                newRow["PATH"] = path;
                newRow["FILENAME"] = fileName;
                dt.Rows.Add(newRow);

                dtaddress.Tables.Add(dt);

            }

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["DocDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;

        }

        public JsonResult DeleteDocDetails(int docid)
        {
            JsonResult json = new JsonResult();
            DataSet dtaddress = (DataSet)Session["SaveAddressDetails"];

            var query = dtaddress.Tables["DocDetails"].AsEnumerable().Where(r => r.Field<Int32>("ID") == docid);

            foreach (var row in query.ToList())
                row.Delete();

            Session["SaveAddressDetails"] = dtaddress;

            string returnData = GetJson(dtaddress.Tables["DocDetails"]);
            json.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            json.Data = returnData;
            return json;
        }

    }
}